using System.Diagnostics;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Globalization;
using ICSharpCode.SharpZipLib.Zip;
using ICSharpCode.SharpZipLib.Core;
class Program
{
    static async Task Main(string[] args)
    {
        // 设置控制台窗口标题
        Console.Title = "Spreadsheet Cleaner - 公众号：旺丁旺财杂货铺";
        Stopwatch stopwatch = new Stopwatch();
        stopwatch.Start();
        // 设置控制台背景颜色和字体颜色
        Console.BackgroundColor = ConsoleColor.White; 
        Console.ForegroundColor = ConsoleColor.DarkRed; 
        Console.Clear(); // 清除控制台并应用新的颜色设置
        // 获取控制台输入的文件路径
        string[] filePaths = args.Length > 0 ? args : GetFilePathsFromConsole();

        // 创建临时文件夹
        string tempFolderPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "temp");
        if (!Directory.Exists(tempFolderPath))
        {
            Directory.CreateDirectory(tempFolderPath);
        }
        // 处理每个文件
        foreach (string filePath in filePaths)
        {
            if (IsValidExcelFile(filePath) && File.Exists(filePath))
            {
                string processedFilePath = filePath;
                // 对于 *.xls 文件，转换为 *.xlsm
                if (Path.GetExtension(filePath).ToLowerInvariant() == ".xls")
                {
                    processedFilePath = ConvertXlsToXlsm(filePath);
                }
                else 
                {
                    string backupFilePath = Path.ChangeExtension(filePath, ".bak");
                    File.Copy(filePath, backupFilePath, true);
                }

                try
                {
                    // 创建临时文件路径
                    string tempFilePath = Path.Combine(tempFolderPath, Path.GetFileName(filePath));

                    using (SpreadsheetDocument document = SpreadsheetDocument.Open(processedFilePath, true))
                    {
                        WorkbookPart workbookPart = document.WorkbookPart;
                        Workbook workbook = workbookPart.Workbook;
                        Console.WriteLine($"正在处理文件: {processedFilePath}");
                        // 创建任务来删除未使用的样式和失效的自定义名称
                        Task<int> deleteStylesTask = Task.Run(() => DeleteUnusedStyles(workbookPart));
                        Task<int> deleteNamesTask = Task.Run(() => DeleteInvalidNames(workbookPart)); 
                        Task deleteUnusedRangesTask = Task.Run(() => DeleteUnusedRanges(document));                  

                        // 等待所有任务完成并获取结果
                        await Task.WhenAll(deleteStylesTask, deleteNamesTask, deleteUnusedRangesTask);
                        int deletedStylesCount = deleteStylesTask.Result;
                        int deletedNamesCount = deleteNamesTask.Result;
                        // int deletedStylesCount = DeleteUnusedStyles(workbookPart);
                        // int deletedNamesCount = DeleteInvalidNames(workbookPart);
                        // DeleteUnusedRanges(document);

                        // 保存修改后的工作簿
                        // workbook.Save();
                        // 输出处理结果
                        Console.WriteLine($"【处理完成: {processedFilePath}】");
                        Console.WriteLine($"【删除了 {deletedStylesCount} 个未使用的样式和 {deletedNamesCount} 个失效的自定义名称。】");
                    }
                    // 将清理后的文件复制到临时文件路径
                    File.Copy(processedFilePath, tempFilePath, true);

                    // 使用更高压缩率重新压缩文件并保存回原文件
                    CompressFileWithHigherCompression(tempFilePath, processedFilePath);

                    // 删除临时文件
                    File.Delete(tempFilePath);
                }

                catch (IOException ex)
                {
                    Console.WriteLine($"文件 {processedFilePath} 被其他进程占用，无法打开。错误信息: {ex.Message}");
                }
            }
            else
            {
                Console.WriteLine($"文件不存在或格式不正确: {filePath}");
            }
        }
        // 停止计时器
        stopwatch.Stop();

        // 输出总耗时
        Console.WriteLine($"总耗时: {stopwatch.Elapsed.TotalSeconds} 秒");
        Console.WriteLine($"----------------------------------------------------------------------------------");
        Console.WriteLine($"本程序基于github项目：https://github.com/davecra/ExcelFileCleaner/ 修改");
        Console.WriteLine();
        Console.WriteLine($"欢迎关注我的公众号: “旺丁旺财杂货铺” ");
        Console.WriteLine($"----------------------------------------------------------------------------------");
        Console.WriteLine("按任意键继续...");
        Console.ReadKey();
    }

    static string[] GetFilePathsFromConsole()
    {
        Console.WriteLine("请将Excel文件拖放到此窗口并按Enter键:");
        string input = Console.ReadLine();
        return input.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
    }
    private class styleCount
    {
        public styleCount(CellStyle cs, CellFormat csf, bool b)
        {
            Found = false;//未被使用:false，已使用：true
            cellStyleFormat = csf;
            cellStyle = cs;
            builtIn = b;
            if (b)
            inUse = true;
        }
        public CellStyle cellStyle { get; set; }
        public CellFormat cellStyleFormat { get; set; }
        public CellFormat cellFormat { get; set; }
        public bool inUse { get; set; }
        public bool builtIn { get; set; }
        public bool Found { get; set; }
    }

    static int DeleteUnusedStyles(WorkbookPart workbookPart)
    {
        int deletedCount = 0;
        Stylesheet stylesheet = workbookPart.WorkbookStylesPart.Stylesheet;
            List<CellFormat> cfList = new List<CellFormat>();
            foreach (CellFormat cf in stylesheet.CellStyleFormats)
                cfList.Add(cf);
            // 获取style列表
            Dictionary<int,styleCount> styleList = new Dictionary<int,styleCount>();
            foreach (CellStyle cs in stylesheet.CellStyles)
            {
                try
                {
                    //添加所有style和单元格格式
                    int formatId = int.Parse(cs.FormatId);
                    styleList.Add(formatId, new styleCount(cs, cfList[formatId], !(cs.BuiltinId == null)));
                }
                catch { } // 忽略
            }
            // 删除所有与单元格无关的东西
            int counter = 0;
            foreach (CellFormat xf in stylesheet.CellFormats)
            {
                try
                {
                    int formatId = int.Parse(xf.FormatId);
                    if (styleList.Keys.Contains(formatId))
                    {
                        styleList[formatId].inUse = true;
                        styleList[formatId].Found = true;
                        styleList[formatId].cellFormat = xf;
                        counter++;
                    }
                }
                catch { } // ignore
            }
             foreach (KeyValuePair<int,styleCount> c in styleList)
            {
                // 删除未使用和未原生style
                if (c.Value.Found == false && c.Value.builtIn == false)
                {
                    c.Value.cellStyleFormat.Remove();
                    c.Value.cellStyle.Remove();
                    deletedCount++;
                    Console.WriteLine($"<范无咎>\"{c.Value.cellStyle.Name}\"样式未使用，已删除。");
                }
            } 
            //删除 styleList 中未使用的style                      
            for (int i = (styleList.Count - 1); i >= 0; i--)
            {
                int keyVal = styleList.Keys.ToList<int>()[i];
                if (styleList[keyVal].inUse == false && styleList[keyVal].builtIn == false)
                    styleList.Remove(keyVal);
            }
           foreach (KeyValuePair<int, styleCount> c in styleList)
            {
                int idx = 0; // 对样式表中剩余的单元格样式格式 (xsf) 集合进行循环，以跟踪索引。
                //如果 cellstyleformat 与我们通过 styleList 在集合中引用的 cellstyleformat 相同，
                //那么我们将更新样式表中的 cellStyle xfId 和 cellFormat xfId，以引用新的索引位置...
                foreach (CellFormat xsf in stylesheet.CellStyleFormats)
                {
                    // CellStyleFormat (refersTo>) CellFormat
                    // CellStyle (refersTo>) CellFormat
                    if (c.Value.cellStyleFormat == xsf)
                    {
                        if (c.Value.Found) // only if matched to a cellformat
                        {
                            // we have a match... index them
                            c.Value.cellStyle.FormatId.Value = (uint)idx;
                            c.Value.cellFormat.FormatId.Value = (uint)idx;
                        }
                        break;
                    }
                    idx++;
                }
            }

            // update counts
            stylesheet.CellStyles.Count.Value = (uint)styleList.Count;
            stylesheet.CellStyleFormats.Count.Value = (uint)styleList.Count;    
    
            // clean
            stylesheet.Save();
            stylesheet = null;
            Console.WriteLine("<范无咎>已完成任务---------------------------------------------------------");
        return deletedCount;
    }

    static int DeleteInvalidNames(WorkbookPart workbookPart)
    {
        int deletedCount = 0;
        List<DefinedName> namesToDelete = new List<DefinedName>();

        // 遍历所有自定义名称，标记失效的名称
        foreach (DefinedName definedName in workbookPart.Workbook.DefinedNames.Elements<DefinedName>())
        {
            if (IsNameInvalid(definedName))
            {
                namesToDelete.Add(definedName);
            }
        }

        // 删除失效的名称
        foreach (DefinedName definedName in namesToDelete)
        {
            workbookPart.Workbook.DefinedNames.RemoveChild(definedName);
            deletedCount++;
            Console.WriteLine($"<谢必安> \"{definedName.Name}\"名称失效，已删除。");
        }
        Console.WriteLine("<谢必安>已完成任务---------------------------------------------------------");
        return deletedCount;
    }

    static bool IsNameInvalid(DefinedName definedName)
    {
        // 获取自定义名称的引用范围
        string nameValue = definedName.Text;

        // 检查名称是否为空
        if (string.IsNullOrEmpty(nameValue))
        {
            return true;
        }

        // 检查名称是否包含特定的无效字符串
        if (nameValue.Contains("#REF") ||
            nameValue.Contains("#N/A") ||
            nameValue.Contains(":\\") ||
            nameValue.Contains("\\") ||
            (nameValue.Contains("[") && nameValue.Contains("]")))
        {
            return true;
        }

        return false;
    }

    static bool IsValidExcelFile(string filePath)
    {
        string[] validExtensions = { ".xlsx", ".xlsm", ".xls" };
        string extension = Path.GetExtension(filePath).ToLowerInvariant();
        return validExtensions.Contains(extension);
    }

    static string ConvertXlsToXlsm(string filePath)
    {
        string newFilePath = Path.ChangeExtension(filePath, ".xlsm");
        string exePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "xls2xlsm.exe");
        string backupFilePath = Path.ChangeExtension(filePath, ".bak");
  
        if (!File.Exists(exePath))
        {
            Console.WriteLine($"转换工具 {exePath} 不存在。");
            return filePath; // 如果转换工具不存在，返回原始文件路径
        }

        try
        {
            ProcessStartInfo startInfo = new ProcessStartInfo
            {
                FileName = exePath,
                Arguments = $"\"{filePath}\"",
                RedirectStandardOutput = true,
                UseShellExecute = false,
                CreateNoWindow = true
            };

            using (Process process = new Process { StartInfo = startInfo })
            {
                process.Start();
                string output = process.StandardOutput.ReadToEnd();
                process.WaitForExit();

                if (process.ExitCode != 0)
                {
                    Console.WriteLine($"转换文件时出错: {output}");
                    return filePath; // 如果转换失败，返回原始文件路径
                }

                if (!File.Exists(newFilePath))
                {
                    Console.WriteLine($"转换后的文件 {newFilePath} 不存在。");
                    return filePath; // 如果转换后的文件不存在，返回原始文件路径
                }
                File.Move(filePath, backupFilePath, true);
                return newFilePath;
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"转换文件时出错: {ex.Message}");
            return filePath; // 如果转换失败，返回原始文件路径
        }
    }

    static void DeleteUnusedRanges(SpreadsheetDocument document)
    {
        foreach (Sheet s in document.WorkbookPart.Workbook.Sheets)
        {
            string relationshipId = s.Id.Value;
            try
            {
                // this will fail if we have a chart sheet
                WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
                // Get the cell at the specified column and row.
                Console.WriteLine($"<钟馗>\"" + s.Name+"\"正在分析...");
                try
                {
                    CleanCells(worksheetPart.Worksheet, s.Name);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("<钟馗>清理失败: " + ex.Message);
                    // we will continue on at this point. There could be
                    // a number of reasons this failed, but since it is
                    // likely not a complete failure, we continue...
                }
                Console.WriteLine($"<钟馗>\"" + s.Name + "\"已完成分析");
                worksheetPart = null;
            }
            catch
            {
                // workbook cast fails...
                // we will ignore this and continue on.
            }
            // tick an update on progress
        }
    }

    private static void CleanCells(Worksheet worksheet, string workbookName)
    {
        // 获取sheetData
        SheetData sheetData = worksheet.GetFirstChild<SheetData>();
        int totalNumRows = sheetData.Elements<Row>().Count();
        if (totalNumRows == 0)
        {
            //空白sheet，sheetData为空，直接返回
            return;
        }

        // *** CATALOG THE USED RANGE ***
        double[] maxVals = getUsedRange(sheetData, workbookName);
        // now verify there are cells to clean
        if (maxVals[2] == 0)
        {
            return;
        }
        else
        {
            // we state we are reviewing these cells since it will
            // likely not actually require this many cells to be 
            // removed. The calculations are based on all cells in
            // the entire range being activated and this is rarely
            // the case. So we need to "review" them.
            Console.WriteLine($"<钟馗>正在检查\" {workbookName}\"{maxVals[2].ToString("#,#", CultureInfo.InvariantCulture)} 个单元格。");
        }

        // DELETE THE UNUSED RANGE AND CATALOG STYLES
        // for very large sheet ranges with hundreds of thousands to a 
        // million rows this process can take a very long time so we will
        // update the status with information on how long it will take
        IEnumerable<Row> rows = sheetData.Elements<Row>();
        List<Row> rowsToDelete = new List<Row>();
        int delCnt = 0;

        foreach (Row r in rows)
        {
            List<Cell> cells = r.Elements<Cell>().ToList();
            if (cells.Count == 0)
            {
                // A cell does not exist at the specified column, in the specified row.
                rowsToDelete.Add(r);
                continue;
            }

            List<Cell> cellsToDelete = new List<Cell>();
            foreach (Cell c in cells)
            {
                // remove cells -- if the user has set this value
                int[] rc = splitRC(c.CellReference.Value);
                if (rc[1] > maxVals[1] || rc[0] > maxVals[0])
                {
                    cellsToDelete.Add(c);
                    continue; // done
                }
                // clean-up
                rc = null;
            }

            // are there cells to delete? If there are then
            // we will delete them here
            if (cellsToDelete.Count > 0)
            {
                // delete all the cells
                foreach (Cell c in cellsToDelete)
                {
                    try
                    {
                        c.Remove();
                        delCnt++;
                    }
                    catch
                    {
                        Console.WriteLine($"<钟馗>不能删除单元格 {c.CellReference.Value}");
                    }
                }
            }

            // Now that all the cells were deleted on the row, it is
            // actually empty? If so, then we need to delete the row...
            // is the row now empty?
            if (r.Elements<Cell>().Count() == 0)
            {
                rowsToDelete.Add(r);
            }
        }

        // if we remove a row, update the log with the count and
        // then re-analyze the sheet
        if (rowsToDelete.Count > 0)
        {
            foreach (Row r in rowsToDelete)
            {
                try
                {
                    r.Remove();
                }
                catch
                {
                    Console.WriteLine($"<钟馗>不能删除行 {r.RowIndex.Value.ToString()}");
                }
            }

            Console.WriteLine($"<钟馗>\"{workbookName}\"已清理 {delCnt.ToString("#,#", CultureInfo.InvariantCulture)} 个单元格。");
            Console.WriteLine($"<钟馗>再次分析\"{workbookName}\"...");
            getUsedRange(sheetData, workbookName);
        }
        else
        {
            // otherwise everything is good.
            Console.WriteLine($"<钟馗>\"{workbookName}\"无需清理。");
        }
        // clean up
        sheetData = null;
        maxVals = null;
    }

    //获取行集合，遍历行和列，然后将未使用的范围编集到一个数组中
    //返回一个数组，第一个元素是最大列数，第二个元素是最大行数，第三个元素是未使用的单元格数
    private static double[] getUsedRange(SheetData sheetData, string workbookName)
    {
        int maxRow = 0;
        int maxCol = 0;
        int colCount = 0;
        int cnt = 0;
        IEnumerable<Row> rows = sheetData.Elements<Row>();
        foreach (Row r in rows)
        {
            List<Cell> cells = r.Elements<Cell>().ToList();
            if (!cells.Any())
            {
                // 在指定行的指定列上不存在单元格。
                continue;
            }

            foreach (Cell c in cells)
            {
                cnt++;
                if (cnt > 0 && cnt % 10000 == 0)
                {
                    Console.WriteLine($"<钟馗>正在分析表格\"{workbookName}区段\"(" + cnt.ToString() + ")... 请稍等");
                }
                int[] rc = splitRC(c.CellReference.Value);
                if (colCount < rc[0])
                {
                    colCount = rc[0];
                }
                if ((c.CellValue != null && c.CellValue.Text.Length > 0) ||
                    (c.CellFormula != null && c.CellFormula.Text.Length > 0))
                {
                    // get max values...    
                    if (rc[1] > maxRow)
                    {
                        maxRow = rc[1];
                    }
                    if (rc[0] > maxCol)
                    {
                        maxCol = rc[0];
                    }
                }
            }
        }
        double extraCells = (rows.Count() * colCount) - (maxRow * maxCol);
        Console.WriteLine("<钟馗>\"" + workbookName + "\"数据已使用 - " + rows.Count() + "行," + colCount.ToString() + "列");
        Console.WriteLine("<钟馗>\"" + workbookName + "\"实际应使用 - " + maxRow.ToString() + "行," + maxCol.ToString() + "列");

        double[] retVal = new double[3];
        retVal[0] = maxCol;
        retVal[1] = maxRow;
        retVal[2] = extraCells; //无效单元格总数

        return retVal;
    }

    //函数用于从 XML 中读取字符串数据，并从 XML 中拆分出行/列信息，返回一个二维数组 的二维数组
    private static int[] splitRC(string cellReference)
    {
        int columnIndex = 0;
        int rowIndex = 0;
        int i = 0;

        // 获取列部分（字母）和行部分（数字）
        while (i < cellReference.Length)
        {
            // 处理字母部分
            if (char.IsLetter(cellReference[i]))
            {
                columnIndex = columnIndex * 26 + (cellReference[i] - 'A' + 1);
            }
            // 处理数字部分
            else if (char.IsDigit(cellReference[i]))
            {
                rowIndex = rowIndex * 10 + (cellReference[i] - '0');
            }
            else
            {
                throw new ArgumentException("Invalid character in cell reference.", cellReference);
            }
            i++;
        }

        return new int[] { columnIndex, rowIndex };
    }
    static void CompressFileWithHigherCompression(string sourceFilePath, string destinationFilePath)
    {
        string tempExtractPath = Path.Combine(Path.GetTempPath(), Path.GetFileNameWithoutExtension(sourceFilePath));
        if (Directory.Exists(tempExtractPath))
        {
            Directory.Delete(tempExtractPath, true);
        }
        Directory.CreateDirectory(tempExtractPath);

        // 解压源文件到临时目录
        using (ZipFile zipFile = new ZipFile(sourceFilePath))
        {
            foreach (ZipEntry entry in zipFile)
            {
                if (!entry.IsFile)
                {
                    continue;
                }

                string entryFileName = entry.Name;
                string fullZipToPath = Path.Combine(tempExtractPath, entryFileName);

                string directoryName = Path.GetDirectoryName(fullZipToPath);
                if (directoryName.Length > 0)
                {
                    Directory.CreateDirectory(directoryName);
                }

                using (Stream zipStream = zipFile.GetInputStream(entry))
                using (FileStream fsOutput = File.Create(fullZipToPath))
                {
                    byte[] buffer = new byte[4096];
                    StreamUtils.Copy(zipStream, fsOutput, buffer);
                }
            }
        }

        // 重新压缩文件
        using (ZipOutputStream zipOutputStream = new ZipOutputStream(File.Create(destinationFilePath)))
        {
            zipOutputStream.SetLevel(5); // 设置压缩等级为 Level 4

            string[] files = Directory.GetFiles(tempExtractPath, "*", SearchOption.AllDirectories);
            foreach (string file in files)
            {
                string relativePath = file.Substring(tempExtractPath.Length + 1);
                ZipEntry entry = new ZipEntry(relativePath)
                {
                    DateTime = DateTime.Now
                };
                zipOutputStream.PutNextEntry(entry);

                using (FileStream fsInput = File.OpenRead(file))
                {
                    byte[] buffer = new byte[4096];
                    int bytesRead;
                    while ((bytesRead = fsInput.Read(buffer, 0, buffer.Length)) > 0)
                    {
                        zipOutputStream.Write(buffer, 0, bytesRead);
                    }
                }

                zipOutputStream.CloseEntry();
            }
        }

        // 删除临时目录
        Directory.Delete(tempExtractPath, true);
    }
}